﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using HotelSoft.HotelManagement_DTO;
using System.Collections;
using System.Data.OleDb;
using System.Data.SqlClient;
//using HotelSoft.HotelManagement_DAO;
namespace HotelSoft.HotelManagement_DAO
{
    public class PhongDAO : AbstractDAO
    {
        public static DataTable CapNhatPhongdondulieus(object AbstractDTO)
        {
            PhongDTO dto = (PhongDTO)AbstractDTO;
            string updateCommand = "UPDATE PHONG SET TrangThai = N'Đang thuê' WHERE SoPhong = '" + dto.SOPHONG + "'";
            return Config.AccessHelper.ExecuteQuery(updateCommand);
        }

        public static bool CapNhatPhongdondulieu(object AbstractDTO)
        {
            try
            {
                PhongDTO dto = (PhongDTO)AbstractDTO;
                string updateCommand = "UPDATE PHONG SET TrangThai = N'Trống' WHERE SoPhong = '" + dto.SOPHONG + "'";
                Config.AccessHelper.ExecuteQuery(updateCommand);
                return true;
            }
            catch {
                return false;
            }
        }

        public static DataTable SelectAll()
        {
            string sql = "SELECT * FROM PHONG";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public static DataTable SelectAlls()
        {
            string sql = "SELECT * FROM PHONG WHERE TrangThai = N'Trống'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public override bool Them(object AbstractDTO)
        {
            try
            {
                PhongDTO dto = (PhongDTO)AbstractDTO;
                string insertCommand = "INSERT INTO PHONG VALUES('" +
                   dto.SOPHONG + "', '" +
                   dto.SOKHACHTOIDA + "', N'" +
                   dto.TRANGTHAI + "', N'" +
                   dto.MOTA + "', '" +
                   dto.TENLP + "')";
                Config.AccessHelper.ExecuteQuery(insertCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }


        public override bool CapNhatThongTin(object AbstractDTO)
        {
            try
            {
                PhongDTO dto = (PhongDTO)AbstractDTO;
                string updateCommand = "UPDATE PHONG SET  SoKhachToiDa = " + dto.SOKHACHTOIDA + ", TrangThai = N'"
                    + dto.TRANGTHAI + "', MoTa = N'" + dto.MOTA + "', TenLP = '" + dto.TENLP +
                     "' WHERE SoPhong = " + dto.SOPHONG;
                Config.AccessHelper.ExecuteQuery(updateCommand);
                return true;
            }
            catch
            {
                return false;
            }
        }

        public override void TraCuu(object AbstractDTO)
        {
            PhongDTO dto = (PhongDTO)AbstractDTO;
            string searchCommand = "Select*from PHONG WHERE";
            string sTest = "Select * from PHONG WHERE";//Dung de kiem tra cac dieu kien truy van
            if (dto.SOPHONG !=null)
            {
                searchCommand += " SoPhong = '" + dto.SOPHONG + "'";
            }
            if (dto.TENLP != null)
            {
                if (searchCommand == sTest)
                    searchCommand += " TenLP = '" + dto.TENLP + "'";
                else
                    searchCommand += ", TenLP = '" + dto.TENLP + "'";
            }
            if(dto.TRANGTHAI !=null)
               if (searchCommand == sTest)
                    searchCommand += " TrangThai = '" + dto.TRANGTHAI + "'";
                else
                    searchCommand += ", TrangThai = '" + dto.TRANGTHAI + "'";
            
            if (dto.SOKHACHTOIDA !=0)
            {
                if (searchCommand == sTest)
                    searchCommand += " SoKhachToiDa = '" + dto.SOKHACHTOIDA + "'";
                else
                    searchCommand += ", SoKhachToiDa = '" + dto.SOKHACHTOIDA + "'";
            }
            if (searchCommand == sTest)
                searchCommand = "Select*from PHONG";
            Config.AccessHelper.ExecuteQuery(searchCommand);
        }


        public bool SoSanhChuoi(String a, String b)
        {
            String a1 = a.ToLower();
            String b1 = b.ToLower();
            a1 = a1.Trim();
            b1 = b1.Trim();
            if (a1.Contains(b1))
                return true;
            return false;
        }
        public ArrayList TraCuuPhong(ArrayList dsPhong, PhongDTO dtoPhongCanTraCuu)
        {
            ArrayList dsKetQuaTraCuu = new ArrayList();
            for (int i = 0; i < dsPhong.Count; i++)
            {
                if (SoSanhTungPhong(((PhongDTO)dsPhong[i]), dtoPhongCanTraCuu) == true)
                    dsKetQuaTraCuu.Add(((PhongDTO)dsPhong[i]));
            }
            return dsKetQuaTraCuu;
        }
        public bool SoSanhTungPhong(PhongDTO dtoPhong1, PhongDTO dtoPhong2)
        {
            bool flag = false;
            if (dtoPhong2.SOPHONG != "")
                if (dtoPhong1.SOPHONG.ToUpper()== dtoPhong2.SOPHONG.ToUpper())
                    flag = true;
                else
                {
                    return false;
                }
            if (dtoPhong2.TENLP != "")
                if (SoSanhChuoi(dtoPhong1.TENLP.ToString(), dtoPhong2.TENLP.ToString()) == true)
                    flag = true;
                else
                    return false;
            if (dtoPhong2.SOKHACHTOIDA != 0)
                if (dtoPhong1.SOKHACHTOIDA==dtoPhong2.SOKHACHTOIDA)
                    flag = true;
                else
                    return false;
            if (dtoPhong2.TRANGTHAI != "")
                if (SoSanhChuoi(dtoPhong1.TRANGTHAI.ToString(), dtoPhong2.TRANGTHAI.ToString()) == true)
                    flag = true;
                else
                    return false;
            else if (dtoPhong2.MOTA != "")
                if (SoSanhChuoi(dtoPhong1.MOTA.ToString(), dtoPhong2.MOTA.ToString()) == true)
                    flag = true;
                else
                    return false;
            return flag;
        }

        public override void Xoa(object AbstractDTO)
        {
            PhongDTO dto = (PhongDTO)AbstractDTO;
            string deleteCommand = "DELETE FROM PHONG WHERE SoPhong = '" + dto.SOPHONG + "'";
            Config.AccessHelper.ExecuteNonQuery(deleteCommand);
        }
      

        internal void Xoa(ArrayList dsPhong)
        {
            foreach (PhongDTO phong in dsPhong)
            {
                Xoa(phong);
            }
        }

        public ArrayList LayDanhSachPhong()
        {
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand = "Select * from PHONG p,LOAIPHONG lp where p.TenLP = lp.TenLP ";
            DataTable ds =Config.AccessHelper.ExecuteQuery(searchCommand);
            PhongDTO phong;
            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                phong = new PhongDTO();
                phong.SOPHONG = ds.Rows[i]["SoPhong"].ToString();
                phong.TRANGTHAI = ds.Rows[i]["TrangThai"].ToString();
                phong.SOKHACHTOIDA = int.Parse(ds.Rows[i]["SoKhachToiDa"].ToString());
                phong.MOTA = ds.Rows[i]["MoTa"].ToString();
                phong.TENLP = ds.Rows[i]["TenLP"].ToString();
                arrDanhSach.Add(phong);
                i++;
            }
            return arrDanhSach;
        }
        public int LayMaSoPhongCuoiCung()
        {
            string sCommand = "SELECT*FROM PHONG WHERE SoPhong >= (SELECT max(SoPhong) from PHONG)";
            DataTable kq = Config.AccessHelper.ExecuteQuery(sCommand);
            return int.Parse(kq.Rows[0][0].ToString());
        }

        public ArrayList getListPhong(string ma)
        {
            ArrayList arrDanhSach = new ArrayList();
            string searchCommand;
            if(ma=="")
                searchCommand= "Select SoPhong, SoKhachToiDa, MoTa,TrangThai, p.TenLP, Gia from PHONG p, LOAIPHONG lp where p.TenLP=lp.TenLP";
            else
                searchCommand = "Select SoPhong, SoKhachToiDa, MoTa,TrangThai, p.TenLP, Gia from PHONG p, LOAIPHONG lp where p.TenLP=lp.TenLP and SoPhong='" + ma + "'";
            DataTable ds =Config.AccessHelper.ExecuteQuery(searchCommand);
            PhongDTO phong;
            LoaiPhongDTO loaiphong = new LoaiPhongDTO(); ;
            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                phong = new PhongDTO();
                phong.SOPHONG = ds.Rows[i]["SoPhong"].ToString();
                phong.TRANGTHAI = ds.Rows[i]["TrangThai"].ToString();
                phong.MOTA = ds.Rows[i]["MoTa"].ToString();
                phong.SOKHACHTOIDA = int.Parse(ds.Rows[i]["SoKhachToiDa"].ToString());
                loaiphong.GIA = float.Parse(ds.Rows[i]["Gia"].ToString());
                phong.TENLP = ds.Rows[i]["TenLP"].ToString();
                arrDanhSach.Add(phong);
                i++;
            }
            return arrDanhSach;
        }
        public List<PhongDTO> getListPhong1(string ma)
        {
            List<PhongDTO> arrDanhSach = new List<PhongDTO>();
            string searchCommand;
            if (ma == "")
                searchCommand = "Select SoPhong, SoKhachToiDa, MoTa, p.TenLP, Gia from PHONG p, LOAIPHONG lp where p.TenLP=lp.TenLP";
            else
                searchCommand = "Select SoPhong, SoKhachToiDa, MoTa, p.TenLP, Gia from PHONG p, LOAIPHONG lp where p.TenLP=lp.TenLP and p.TenLP='" + ma + "'";
            DataTable ds = Config.AccessHelper.ExecuteQuery(searchCommand);
            PhongDTO phong;
            LoaiPhongDTO loaiphong = new LoaiPhongDTO(); 
            int i = 0;
            int iSoDong = ds.Rows.Count;
            int iSoCot = ds.Columns.Count;
            while (i < iSoDong)
            {
                phong = new PhongDTO();
                phong.SOPHONG = ds.Rows[i]["SoPhong"].ToString();
                phong.SOKHACHTOIDA = int.Parse(ds.Rows[i]["SoKhachToiDa"].ToString());
                //phong.TRANGTHAI = ds.Rows[i]["TrangThai"].ToString();
                phong.MOTA = ds.Rows[i]["MoTa"].ToString();
                phong.TENLP = ds.Rows[i]["TenLP"].ToString();
                loaiphong.GIA= float.Parse(ds.Rows[i]["Gia"].ToString());
                
                arrDanhSach.Add(phong);
               // arrDanhSach.Add(loaiphong);
                i++;
            }
            return arrDanhSach;
        }

        public DataTable dataTablegetListPhong(string ma)
        {
            string sql = "Select SoPhong, SoKhachToiDa, TrangThai,MoTa, p.TenLP, Gia from PHONG p, LOAIPHONG lp where p.TenLP=lp.TenLP and p.TenLP='" + ma + "'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        public DataTable dataTablegetListPhong1(string ma)
        {
            string sql = "Select SoPhong, SoKhachToiDa,MoTa, p.TenLP, Gia from PHONG p, LOAIPHONG lp where p.TenLP=lp.TenLP and p.TenLP='" + ma + "'";
            return Config.AccessHelper.ExecuteQuery(sql);
        }
        public bool KiemTraSoPhong(string Ma)
        {
            PhongDTO dto = new PhongDTO();
            OleDbConnection Cnn = new OleDbConnection();
            string sql = "select Count(*) from PHONG where SoPhong = '" + Ma + "'";
            int So;// = (int)sql.Length;
            OleDbCommand cmd = new OleDbCommand(sql);
            So = (int)cmd.ExecuteScalar();
            if (So > 0)
            //if(sql == dto.SOPHONG)
                return true;
            else
                return false;
        }


        public DataTable report_dulieuPhong(string Maphong)
        {
            //DataSet ds = new DataSet();
            string strSQL = "Select SoPhong , SoKhachToiDa , MoTa , p.TenLP , Gia " +
                           "From PHONG p , LOAIPHONG lp" +
                            "Where p.TenLP = lp.TenLP and SoPhong = '" + Maphong + "'";
            //if (GiaTri != "")
            //    strSQL += " and  "='" + GiaTri + "'";

            //OleDbDataAdapter da = new OleDbDataAdapter(strSQL, Cnn);
            //da.Fill(ds, "RP_HDB");
            return Config.AccessHelper.ExecuteQuery(strSQL);
        }

        public bool KTMA(object AbstractDTO)
        {
            PhongDTO dto = (PhongDTO)AbstractDTO;
            string strSQL;
            strSQL = "Select Count(*) From PHONG where SoPhong='" + dto.SOPHONG + "'";
            return Config.AccessHelper.ExecuteScalar(strSQL);
        }
    }
}
